﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TpData.Interfaces;
using System.ComponentModel;
using System.Data.OleDb;
using System.Data;

namespace TpData.Layer {
    public class BaseAccessDataLayer : IDataLayer {

        #region Members

        private IntPtr handle;
        private Component component = new Component();
        private bool disposed = false;
        private string connectionString;

        #endregion

        #region Constructor

        public BaseAccessDataLayer() {
            Dispose(false);
        }

        #endregion

        #region Initialize

        public void Initialize(string connectionString) {
            this.connectionString = connectionString;
        }

        #endregion

        #region Properties

        protected string ConnectionString {
            get { return this.connectionString; }
        }

        #endregion

        #region Methods

        protected void CloseConnection(OleDbConnection conn) {
            try {
                conn.Close();
            } catch { }
        }

        public void Dispose() {
            Dispose(true);
            // This object will be cleaned up by the Dispose method.
            // Therefore, you should call GC.SupressFinalize to
            // take this object off the finalization queue
            // and prevent finalization code for this object
            // from executing a second time.
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing) {
            // Check to see if Dispose has already been called.
            if (!this.disposed) {
                // If disposing equals true, dispose all managed
                // and unmanaged resources.
                if (disposing) {
                    // Dispose managed resources.
                    component.Dispose();
                }

                // Call the appropriate methods to clean up
                // unmanaged resources here.
                // If disposing is false,
                // only the following code is executed.
                CloseHandle(handle);
                handle = IntPtr.Zero;

                // Note disposing has been done.
                disposed = true;

            }
        }

        #endregion

        #region Functions

        protected OleDbConnection OpenConnection() {
            OleDbConnection conn = new OleDbConnection(this.ConnectionString);
            conn.Open();
            return conn;
        }

        #endregion

        #region DataReader

        protected virtual OleDbDataReader GetDataReader(string sql) {
            OleDbCommand cmd = new OleDbCommand(sql, this.OpenConnection());
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        protected virtual OleDbDataReader GetDataReader(string sql, params OleDbParameter[] parameters) {
            OleDbCommand cmd = new OleDbCommand(sql, this.OpenConnection());
            if (parameters != null) {
                foreach (OleDbParameter parameter in parameters) {
                    if (parameter != null) {
                        cmd.Parameters.Add(parameter);
                    }
                }
            }
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        protected virtual OleDbDataReader GetDataReaderFromStoredProcedure(string name, params OleDbParameter[] parameters) {
            OleDbCommand cmd = new OleDbCommand(name, this.OpenConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null) {
                foreach (OleDbParameter parameter in parameters) {
                    if (parameter != null) {
                        cmd.Parameters.Add(parameter);
                    }
                }
            }
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion

        #region DataTable

        protected virtual DataTable GetDataTable(string sql) {
            return GetDataTable(sql, null, new OleDbParameter[] { });
        }

        protected virtual DataTable GetDataTable(string sql, params OleDbParameter[] parameters) {
            return GetDataTable(sql, null, parameters);
        }

        protected virtual DataTable GetDataTable(string sql, DataTable table, params OleDbParameter[] parameters) {
            using (OleDbConnection connection = this.OpenConnection()) {
                OleDbCommand cmd = new OleDbCommand(sql, connection);
                if (parameters != null) {
                    foreach (OleDbParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                if (table == null) {
                    table = new DataTable();
                }
                adapter.Fill(table);
                return table;
            }
        }

        protected virtual DataTable GetDataTableFromStoredProcedure(string name, params OleDbParameter[] parameters) {
            using (OleDbConnection connection = this.OpenConnection()) {
                OleDbCommand cmd = new OleDbCommand(name, connection);
                cmd.CommandType = CommandType.StoredProcedure;

                if (parameters != null) {
                    foreach (OleDbParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                DataTable table = new DataTable();
                adapter.Fill(table);
                return (table);
            }
        }

        #endregion

        #region Executes

        protected virtual int ExecuteNonQuery(string sql) {
            using (OleDbConnection connection = this.OpenConnection()) {
                OleDbCommand cmd = new OleDbCommand(sql, connection);
                return cmd.ExecuteNonQuery();
            }
        }

        protected virtual int ExecuteNonQuery(string sql, params OleDbParameter[] parameters) {
            using (OleDbConnection connection = this.OpenConnection()) {
                OleDbCommand cmd = new OleDbCommand(sql, connection);
                if (parameters != null) {
                    foreach (OleDbParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                return cmd.ExecuteNonQuery();
            }
        }

        #endregion

        #region Basics

        protected object CheckNull(object value) {
            if (value == null) {
                return DBNull.Value;
            }
            return value;
        }

        protected object CheckNull(DateTime dt) {
            if ((dt == DateTime.MinValue) || (dt == DateTime.MaxValue)) return DBNull.Value;
            return dt;
        }

        protected object CheckNull(Guid guid) {
            if (guid == Guid.Empty) return DBNull.Value;
            return guid;
        }

        protected string GetString(IDataReader reader, string field) {
            return (reader[field] as string);
        }

        protected byte GetByte(IDataReader reader, string field) {
            if (reader[field] != DBNull.Value) return (byte)reader[field];
            return 0;
        }

        protected long GetLong(IDataReader reader, string field) {
            if (reader[field] != DBNull.Value) return (Int32)reader[field];
            return 0;
        }

        protected Guid GetGuid(IDataReader reader, string field) {
            if (reader[field] != DBNull.Value) return (Guid)reader[field];
            return Guid.Empty;
        }

        protected DateTime GetDateTime(IDataReader reader, string field) {
            return this.GetDateTime(reader, field, DateTime.MinValue);
        }

        protected DateTime GetDateTime(IDataReader reader, string field, DateTime defaultValue) {
            if (reader[field] != DBNull.Value) return (DateTime)reader[field];
            return defaultValue;
        }

        protected int GetInt32(IDataReader reader, string field) {
            return this.GetInt32(reader, field, 0);
        }

        protected short GetShort(IDataReader reader, string field) {
            return this.GetShort(reader, field, 0);
        }

        protected short GetShort(IDataReader reader, string field, short defaultValue) {
            if (reader[field] != DBNull.Value) return (short)reader[field];
            return defaultValue;
        }

        protected int GetInt32(IDataReader reader, string field, int defaultValue) {
            if (reader[field] != DBNull.Value) return (int)reader[field];
            return defaultValue;
        }

        protected long GetInt64(IDataReader reader, string field) {
            return this.GetInt64(reader, field, 0);
        }

        protected long GetInt64(IDataReader reader, string field, int defaultValue) {
            if (reader[field] != DBNull.Value) return (long)reader[field];
            return defaultValue;
        }

        protected decimal GetDecimal(IDataReader reader, string field) {
            return this.GetDecimal(reader, field, 0);
        }

        protected decimal GetDecimal(IDataReader reader, string field, long defaultValue) {
            if (reader[field] != DBNull.Value) return (decimal)reader[field];
            return defaultValue;
        }

        protected bool GetBoolean(IDataReader reader, string field) {
            return this.GetBoolean(reader, field, false);
        }

        protected bool GetBoolean(IDataReader reader, string field, bool defaultValue) {
            if (reader[field] != DBNull.Value) return (bool)reader[field];
            return defaultValue;
        }

        protected DataRelation CreateDataRelation(string relationName, DataSet ds, string parentColumn, string childColumn) {
            string[] parent = parentColumn.Split('.');
            if (parent.Length != 2) {
                throw new ArgumentException("parentColumn must contain valid column name: \"table.colum\"", "parentColumn");
            }

            string[] child = childColumn.Split('.');
            if (child.Length != 2) {
                throw new ArgumentException("childColumn must contain valid column name: \"table.colum\"", "childColumn");
            }

            return new DataRelation(relationName, ds.Tables[parent[0]].Columns[parent[1]], ds.Tables[child[0]].Columns[child[1]]);
        }

        protected void MergeAndUpdate(DataTable newData, string sql, params OleDbParameter[] parameters) {
            using (OleDbConnection connection = this.OpenConnection()) {
                OleDbCommand cmd = new OleDbCommand(sql, connection);
                if (parameters != null) {
                    foreach (OleDbParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds, newData.TableName);

                OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
                ds.Merge(newData, false, MissingSchemaAction.Ignore);
                adapter.Update(ds.Tables[0]);
            }
        }

        #endregion

        #region DLL Calls

        [System.Runtime.InteropServices.DllImport("Kernel32")]
        private extern static Boolean CloseHandle(IntPtr handle);

        #endregion

    }
}
